﻿-- =========================================================================================================
-- Author:		MICHAEL RITACCO
-- Copyright:	2010 MEKHANO GROUP LLC
-- Version:		08/21/2010 1.0
-- =========================================================================================================
CREATE PROCEDURE [flcr].[api_service_dml_upsert]
	@TVP as flcr.SERVICE_UDT READONLY
AS	

BEGIN TRY
  
	MERGE	[flcr].[SERVICE] a
	USING	(SELECT	[SERVICE_ID]
					,[AUDIT_USER]
					,[AUDIT_DTTM]
					,[AUDIT_DTTM_TZ]
					,[AUDIT_MOD_USER]
					,[AUDIT_MOD_DTTM]
					,[AUDIT_MOD_DTTM_TZ]
					,[RLS_MASK]
					,[RLS_OWNER]
					,[BUILTIN]
					,[WRKFL_ITEM_ID]
					,[WRKFL_STATUS_ID]
					,[ACTIVE_FLG]
					,[SORT_ORDER]
					,[SERVICE_NAME]
					,[SERVICE_SNAME]
					,[SERVICE_DESC]
					,[SERVICE_TYPE]
					,[PARENT_ID]
					,[DOMAIN_ID]
					,[ORG_ID]
					,[RESOURCE_ID]
					,[SUPPLIER_ID]
					,[SLA_ID]
			   FROM	@TVP) b
	    ON	a.[SERVICE_ID] = b.[SERVICE_ID]
	  WHEN MATCHED THEN
			UPDATE SET 
				A.[AUDIT_MOD_USER] = B.[AUDIT_MOD_USER]
				,A.[AUDIT_MOD_DTTM] = B.[AUDIT_MOD_DTTM]
				,A.[AUDIT_MOD_DTTM_TZ] = B.[AUDIT_MOD_DTTM_TZ]
				,A.[RLS_MASK] = B.[RLS_MASK]
				,A.[RLS_OWNER] = B.[RLS_OWNER]
				,A.[WRKFL_ITEM_ID] = B.[WRKFL_ITEM_ID]
				,A.[WRKFL_STATUS_ID] = B.[WRKFL_STATUS_ID]
				,A.[ACTIVE_FLG] = B.[ACTIVE_FLG]
				,A.[SORT_ORDER] = B.[SORT_ORDER]
				,A.[SERVICE_NAME] = B.[SERVICE_NAME]
				,A.[SERVICE_SNAME] = B.[SERVICE_SNAME]
				,A.[SERVICE_DESC] = B.[SERVICE_DESC]
				,A.[SERVICE_TYPE] = B.[SERVICE_TYPE]
				,A.[PARENT_ID] = B.[PARENT_ID]
				,A.[DOMAIN_ID] = B.[DOMAIN_ID]
				,A.[ORG_ID] = B.[ORG_ID]
				,A.[RESOURCE_ID] = B.[RESOURCE_ID]
				,A.[SUPPLIER_ID] = B.[SUPPLIER_ID]
				,A.[SLA_ID] = B.[SLA_ID]
	  WHEN NOT MATCHED THEN	  	  
		INSERT ([AUDIT_USER]
					,[AUDIT_DTTM]
					,[AUDIT_DTTM_TZ]
					,[AUDIT_MOD_USER]
					,[AUDIT_MOD_DTTM]
					,[AUDIT_MOD_DTTM_TZ]
					,[RLS_MASK]
					,[RLS_OWNER]
					,[BUILTIN]
					,[WRKFL_ITEM_ID]
					,[WRKFL_STATUS_ID]
					,[ACTIVE_FLG]
					,[SORT_ORDER]
					,[SERVICE_NAME]
					,[SERVICE_SNAME]
					,[SERVICE_DESC]
					,[SERVICE_TYPE]
					,[PARENT_ID]
					,[DOMAIN_ID]
					,[ORG_ID]
					,[RESOURCE_ID]
					,[SUPPLIER_ID]
					,[SLA_ID])
		VALUES (B.[AUDIT_USER]
					,B.[AUDIT_DTTM]
					,B.[AUDIT_DTTM_TZ]
					,B.[AUDIT_MOD_USER]
					,B.[AUDIT_MOD_DTTM]
					,B.[AUDIT_MOD_DTTM_TZ]
					,B.[RLS_MASK]
					,B.[RLS_OWNER]
					,B.[BUILTIN]
					,B.[WRKFL_ITEM_ID]
					,B.[WRKFL_STATUS_ID]
					,B.[ACTIVE_FLG]
					,B.[SORT_ORDER]
					,B.[SERVICE_NAME]
					,B.[SERVICE_SNAME]
					,B.[SERVICE_DESC]
					,B.[SERVICE_TYPE]
					,B.[PARENT_ID]
					,B.[DOMAIN_ID]
					,B.[ORG_ID]
					,B.[RESOURCE_ID]
					,B.[SUPPLIER_ID]
					,B.[SLA_ID])
		OUTPUT inserted.*;	
				
END TRY
BEGIN CATCH
	EXEC [flcr].[utl_getDBError];
END CATCH